//
//  FMDBUtils.swift
//  SwiftDemo
//
//  Created by apple on 2019/7/7.
//  Copyright © 2019 yimiSun. All rights reserved.
//

import Foundation

//数据库文件
private let K_APP_DB_PATH:String = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last! + "/AppDBFile.sqlite"

/** 用户登录名信息表 */
private let K_FMDB_USER_LOGIN_INFO:String = "users_login_infos"
private let K_FMDB_USER_TOUCHID_LOGIN_INFO:String = "users_touchid_login_info"

/**
 * SQLite 数据库工具类
 */
struct FMDBUtils {
    
    /** FMDatabaseQueue */
    private let dataBaseQueue:FMDatabaseQueue? = FMDatabaseQueue.init(path: K_APP_DB_PATH)
    
    /**
     * SqliteDBUtils 单例
     */
    public static let shareInstance = FMDBUtils.init()
    
    
    //MARK: - 增、删、改、查
    /** 初始化数据库和表 */
    public func initDBAndTables() {
        
        var isSuccess:Bool = false
        
        //创建表 
        let arrSqls:[String] = [
            String.init(format: "CREATE TABLE IF NOT EXISTS `%@`('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'user_name' TEXT UNIQUE,'user_password' TEXT DEFAULT '','is_remember' TINYINT DEFAULT 0);", K_FMDB_USER_LOGIN_INFO),
            String.init(format: "CREATE TABLE IF NOT EXISTS `%@`('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'user_name' TEXT UNIQUE,'user_password' TEXT DEFAULT '','user_remark' TEXT DEFAULT '');", K_FMDB_USER_TOUCHID_LOGIN_INFO),
        ]
        
        dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            for strSQL:String in arrSqls {
                isSuccess = db?.executeUpdate(strSQL, withParameterDictionary: nil) ?? false
                if isSuccess == false {
                    break
                }
            }
        })
        
        if isSuccess == true {
            print("表初始化成功")
        }
        else{
            print("表初始化失败")
        }
    }
    
    /** 插入数据 */
    private func addDataForDic(dic:[String:String]?,
                               AndTable tbName:String) -> Bool {
        if dic == nil || (dic?.keys.count)! <= 0 {
            return false
        }
        
        let strColumns:String = String.init(format: "%@", dic!.keys.joined(separator: ","))
        let strValues:String = String.init(format: "%@", dic!.values.joined(separator: "','"))
        
        // replace into （insert into 的增强版）如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则，直接插入新数据。
        let strSQL:String = String.init(format: "REPLACE INTO `%@`(%@) VALUES('%@');", tbName,strColumns,strValues)
        print(strSQL)
        
        var isOK:Bool = false
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOK = db?.executeUpdate(strSQL, withParameterDictionary: nil) ?? false
        })
        return isOK
    }
    
    /**
     * 获取所有数据
     * @param tbName 表名称
     * @return 返回所有数据的数组
     */
    private func getAllValuesFromTable(tbName:String,
                                        AndWhere strWere:String?,
                                        WithOrderBy strOrderBy:String?) -> [[String:Any]] {
        
        var strColumnInfo:String?
        var arrInfo = [[String:Any]]()
        
        let strSQL:String = String.init(format:"SELECT * FROM `%@` %@ %@;",tbName,strWere ?? "",strOrderBy ?? "")
        print(strSQL)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            let resultSet:FMResultSet? = db?.executeQuery(strSQL, withArgumentsIn: nil)
            defer {
                resultSet?.close()
            }
            
            if resultSet != nil {
                var dicTemp:[String:Any]?
                while resultSet!.next() {
                    dicTemp = [String:Any]()
                    
                    for i in 0..<resultSet!.columnCount() {
                        strColumnInfo = resultSet!.columnName(for:i)
                        
                        if strColumnInfo != nil && !strColumnInfo!.isEmpty {
                            dicTemp![strColumnInfo!] = resultSet!.object(forColumnName: strColumnInfo!)
                        }
                    }
                    
                    //添加到数组
                    arrInfo.append(dicTemp!)
                }
            }
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return arrInfo
    }

    
    /** 根据主键删除信息 */
    private func removeDataForPrimaryKey(strKey:String,
                                         andValue v:String,
                                         andTable tbName:String) -> Bool {
        var isOk:Bool = false;
        let strSQL:String = String.init(format:"DELETE FROM `%@` WHERE `%@` = '%@';",tbName,strKey,v)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOk = db?.executeUpdate(strSQL, withArgumentsIn: nil) ?? false
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return isOk
    }


    /**
     * 删除表中所有信息
     * @param tbName NSString 表名
     */
    private func removeAllDataForTableName(tbName:String) -> Bool {
        var isOk:Bool = false
        let strSQL:String = String.init(format:"DELETE FROM `%@`;",tbName)
        
        self.dataBaseQueue?.inDatabase({ (db:FMDatabase?) in
            isOk = db?.executeUpdate(strSQL, withArgumentsIn: nil) ?? false
        })
        
        defer{
            self.dataBaseQueue?.close()
        }
        
        return isOk
    }
}


//MARK: - 登录密码
extension FMDBUtils {
    
    /** 记住密码 */
    func keepPasswordFor(User uid:String,
                         andPwd pwd:String,
                         withRemember r:Bool = false) -> Bool {
        let dicTemp = [
            "user_name":uid,
            "user_password":pwd,
            "is_remember": r ? "1" : "0"
        ]
        let _r = self.addDataForDic(dic: dicTemp, AndTable: K_FMDB_USER_LOGIN_INFO)
        return _r
    }
    
    /** 查找密码 */
    func getPwdFor(User uid:String,andIskeep k:Bool = true) -> String? {
        let _arrTemp:[[String:Any]]? = self.getAllValuesFromTable(tbName: K_FMDB_USER_LOGIN_INFO,
                                                                  AndWhere: "WHERE `user_name` = '\(uid)' AND `is_remember` = \(k ? 1 : 0)",
                                                                     WithOrderBy: "ORDER BY `id` DESC LIMIT 1")
        
        if _arrTemp != nil && _arrTemp!.count > 0 {
            return _arrTemp!.first!["user_password"] as? String
        }
        else {
            return nil
        }
    }
    
    /** 移除密码 */
    func removePwdFor(User uid:String) -> Bool {
        let result:Bool = self.removeDataForPrimaryKey(strKey: "user_name",
                                                       andValue: uid,
                                                       andTable: K_FMDB_USER_LOGIN_INFO)
        return result
    }
}


//MARK: - TouchID登录
extension FMDBUtils {
    
    /**! 添加TouchID登录信息 */
    func addTouchIdInfoFor(User uid:String,
                         andPwd pwd:String) -> Bool {
        let dicTemp = [
            "user_name":uid,
            "user_password":pwd
        ]
        let _r = self.addDataForDic(dic: dicTemp, AndTable: K_FMDB_USER_TOUCHID_LOGIN_INFO)
        return _r
    }
    
    /**! 移除密码 */
    func removeTouchIDInfoFor(User uid:String) -> Bool {
        let result:Bool = self.removeDataForPrimaryKey(strKey: "user_name",
                                                       andValue: uid,
                                                       andTable: K_FMDB_USER_TOUCHID_LOGIN_INFO)
        return result
    }
    
    /**!
     * 查找最新的TouchID(指纹登录时使用)
     * @return (用户名,密码)
     */
    func getTouchIDInfo() -> (String?,String?) {
        let _arrTemp:[[String:Any]]? = self.getAllValuesFromTable(tbName: K_FMDB_USER_TOUCHID_LOGIN_INFO,
                                                                     AndWhere: nil,
                                                                     WithOrderBy: "ORDER BY `id` DESC LIMIT 1")
        
        if _arrTemp != nil && _arrTemp!.count > 0 {
            return (
                _arrTemp!.first!["user_name"] as? String,
                _arrTemp!.first!["user_password"] as? String
            )
        }
        else {
            return (nil,nil)
        }
    }
    
}

